import pymysql
from todos.todos import Todo
class TodoStrong:
    @staticmethod
    def get_connection():
        return pymysql.Connection(host='192.168.15.129',database='myceshi',user='root',password='Yangyang123.',autocommit=True)

    def create(self,title: str):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute("insert into todos(title) values (%s)",(title,))
            cursor.execute("select last_insert_id()")
            (todo_id,)=cursor.fetchone()
            print(cursor.fetchone())
            return Todo(title,id=todo_id)

    def convert_row_object(self,row):
        return Todo(row[1],id=row[0],completed=True if row[2]==1 else False)

    def get_list(self):
        coon=self.get_connection()
        curson=coon.cursor()
        curson.execute("select * from todos")
        todo_list=curson.fetchall()
        list_l=[]
        for row in todo_list:
            list_l.append(self.convert_row_object(row))
        return list_l

    def init_db(self):
        # 如果todos表不存在，则创建它
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                    CREATE TABLE IF not EXISTS `todos`(
                    `id` int  NOT NULL AUTO_INCREMENT,
                    `title` varchar(300) NOT NULL DEFAULT '',
                    `completed` int NOT NULL DEFAULT b'0',
                    PRIMARY KEY (`id`)
                    ) ENGINE=INNoDB DEFAULT CHARSET=UTF8MB4 COLLATE=utf8mb4_0900_ai_ci;
                    ''')
    def mark_completed(self,todo_id):
        coon = self.get_connection()
        curson = coon.cursor()
        sql="update todos set completed=1 where id="+str(todo_id)
        curson.execute(sql)
    def delete(self,id):
        coon = self.get_connection()
        curson = coon.cursor()
        sql = "delete from todos where id="+str(id)
        curson.execute(sql)

